﻿Public Class ExcelToSql

    Private Shared Function PopulateDataTable(ByRef target As Excel.Range) As Data.DataTable

        Dim _dt As New Data.DataTable

        Try

            For i As Integer = 1 To target.Columns.Count

                Dim t = ColumnDataType(target, i)

                _dt.Columns.Add(New Data.DataColumn(columnName:=CStr(DirectCast(target.Cells(1, i), Excel.Range).Value)))

                With _dt.Columns(i - 1)
                    .DataType = t
                    .AllowDBNull = True
                End With

                If t Is GetType(String) Then
                    _dt.Columns(_dt.Columns.Count - 1).MaxLength = MaxStringLength(target, i)
                End If

            Next

            Dim rangeArray = DirectCast(target.Value, Object(,))

            Dim cols = rangeArray.GetUpperBound(1)
            Dim rows = rangeArray.GetUpperBound(0)

            For i As Integer = 2 To rows
                Dim nr = _dt.NewRow
                For j = 1 To cols
                    If Not rangeArray(i, j) Is Nothing And CStr(rangeArray(i, j))?.ToUpper <> "NULL" Then
                        nr.Item(j - 1) = rangeArray(i, j)
                    End If
                Next
                _dt.Rows.Add(nr)
            Next

            Return _dt

        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

        Return New Data.DataTable

    End Function

    Private Shared Function ColumnDataType(ByVal target As Excel.Range, ByVal columnNumber As Integer) As Type

        Dim isInteger As Boolean = True
        Dim isDouble As Boolean = True
        Dim isDateTime As Boolean = True

        Dim rangeArray = DirectCast(target.Value, Object(,))

        Dim valueAsString As Func(Of Integer, Integer, String) = Function(row, col) CStr(rangeArray(row, col))

        Dim valueIsNull As Func(Of Integer, Integer, Boolean) = Function(row, col) valueAsString(row, col)?.ToUpper = "NULL"

        If rangeArray Is Nothing Then
            Return GetType(String)
        End If

        For i = 2 To rangeArray.GetUpperBound(0)
            Dim j As Integer
            If Integer.TryParse(valueAsString(i, columnNumber), j) = False Then
                If Not rangeArray(i, columnNumber) Is Nothing Then
                    isInteger = False
                    Exit For
                End If
            End If
        Next

        If isInteger = False Then
            For i = 2 To rangeArray.GetUpperBound(0)
                Dim j As Double
                If Double.TryParse(valueAsString(i, columnNumber), j) = False Then
                    If Not rangeArray(i, columnNumber) Is Nothing Then
                        isDouble = False
                        Exit For
                    End If
                End If
            Next
        End If

        If isInteger = False AndAlso isDouble = False Then
            For i = 2 To rangeArray.GetUpperBound(0)
                Dim j As DateTime
                If DateTime.TryParse(valueAsString(i, columnNumber), j) = False Then
                    If Not rangeArray(i, columnNumber) Is Nothing Then
                        isDateTime = False
                        Exit For
                    End If
                End If
            Next
        End If

        rangeArray = Nothing

        If isInteger Then
            Return GetType(Integer)
        ElseIf isDouble Then
            Return GetType(Double)
        ElseIf isDateTime Then
            Return GetType(Date)
        Else
            Return GetType(String)
        End If

    End Function

    Private Shared Function MaxStringLength(ByVal target As Excel.Range, ByVal columnNumber As Integer) As Integer

        Dim targetArray = DirectCast(target.Value, Object(,))

        If targetArray Is Nothing Then
            Return 1
        End If

        Dim maxLength As Integer = 1

        For i = 2 To targetArray.GetUpperBound(0)
            If Not targetArray(i, columnNumber) Is Nothing Then
                Dim sl = CStr(targetArray(i, columnNumber)).Length
                If sl > maxLength Then
                    maxLength = sl
                End If
            End If
        Next

        Return maxLength

    End Function

    Public Shared Function Script(ByRef target As Excel.Range) As String


        Dim _dt = PopulateDataTable(target)

        Dim columnList As New List(Of String)
        For i As Integer = 0 To _dt.Columns.Count - 1
            Dim columnType = _dt.Columns(i).DataType
            If columnType Is GetType(Integer) Then
                columnList.Add(String.Format("[{0}] int", _dt.Columns(i).ColumnName))
            ElseIf columnType Is GetType(Double) Then
                columnList.Add(String.Format("[{0}] float", _dt.Columns(i).ColumnName))
            ElseIf columnType Is GetType(DateTime) Then
                columnList.Add(String.Format("[{0}] datetime", _dt.Columns(i).ColumnName))
            ElseIf columnType Is GetType(String) Then
                columnList.Add(String.Format("[{0}] varchar({1})", _dt.Columns(i).ColumnName, _dt.Columns(i).MaxLength))
            Else
                Throw New Exception("Unsupported Type")
            End If
        Next

        Dim sb As New System.Text.StringBuilder

        Dim quote As Func(Of String, String) = Function(s) String.Format("'{0}'", s.Replace("'", "''"))

        sb.Append(String.Format("create table #t1({0})", columnList.Aggregate(Function(x, y) x & Chr(10) & "," & y)))

        Dim columnCount = _dt.Columns.Count
        sb.Append(vbCrLf)
        For i As Integer = 0 To _dt.Rows.Count - 1
            sb.Append(" insert #t1 values (")
            For j = 0 To columnCount - 1
                If TypeOf _dt.Rows(i).Item(j) Is DBNull Then
                    sb.Append("NULL")
                    If j <> columnCount - 1 Then
                        sb.Append(",")
                    End If
                Else
                    If _dt.Columns(j).DataType Is GetType(String) Or _dt.Columns(j).DataType Is GetType(DateTime) Then
                        If _dt.Rows(i).Item(j).ToString.ToUpper = "NULL" Then
                            sb.Append("NULL")
                        Else
                            sb.Append(quote(CStr(_dt.Rows(i).Item(j))))
                        End If
                    Else
                        sb.Append(_dt.Rows(i).Item(j))
                    End If
                    If j <> columnCount - 1 Then
                        sb.Append(",")
                    End If
                End If
            Next

            sb.Append(")")
            sb.Append(vbCrLf)

        Next

        sb.Append("select * from #t1")

        Return sb.ToString


    End Function

    Public Shared Function ScriptValues(ByRef target As Excel.Range) As String


        ' SELECT * FROM (VALUES('abcdefg',123,3.14),('abcdefg',123,3.14e)) v([stringColumn],[intColumn],[floatColumn])

        Dim _dt = PopulateDataTable(target)

        Dim sb As New System.Text.StringBuilder("SELECT * FROM (VALUES")

        Dim quoteString As Func(Of String, String) = Function(s) String.Format("'{0}'", s.Replace("'", "''"))

        Dim columnCount = _dt.Columns.Count

        For i As Integer = 0 To _dt.Rows.Count - 1
            sb.Append("(")
            For j = 0 To columnCount - 1

                If TypeOf _dt.Rows(i).Item(j) Is DBNull Then
                    sb.Append("NULL")
                    If j <> columnCount - 1 Then
                        sb.Append(",")
                    End If
                Else
                    If _dt.Columns(j).DataType Is GetType(String) Or _dt.Columns(j).DataType Is GetType(DateTime) Then
                        If _dt.Rows(i).Item(j).ToString.ToUpper = "NULL" Then
                            sb.Append("NULL")
                        Else
                            sb.Append(quoteString(CStr(_dt.Rows(i).Item(j))))
                        End If
                    Else
                        sb.Append(_dt.Rows(i).Item(j))
                    End If
                    If j <> columnCount - 1 Then
                        sb.Append(",")
                    End If
                End If

            Next

            If i < _dt.Rows.Count - 1 Then
                sb.Append(")," + vbCrLf)
            Else
                sb.Append(")")
            End If

        Next

        sb.Append(") v(")
        For i As Integer = 0 To _dt.Columns.Count - 1
            sb.Append(String.Format("[{0}]", _dt.Columns(i).ColumnName))
            If i < _dt.Columns.Count - 1 Then
                sb.Append(",")
            Else
                sb.Append(")" + vbCrLf)
            End If
        Next

        Return sb.ToString

    End Function

End Class
